package server.phone.dao;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.utopia84.model.Saleandpdt;
import com.utopia84.model.Salerecord;
import com.utopia84.utils.MyDate;

/**
 * @author utopia
 * 
 */
public class SaleRecordDAO {
	DBHelper manager;
	String sql = "";
	ResultSet rs;

	public SaleRecordDAO() throws IOException, ClassNotFoundException,
			SQLException {
		manager = DBHelper.createInstance();
	}

	/**
	 * 返回所有餐桌区域信息
	 * 
	 * @return
	 * @throws SQLException
	 */
	public List<Salerecord> getAllSaleRecord(String createTime)
			throws SQLException {
		List<Salerecord> sales = new ArrayList<Salerecord>();
		Salerecord sale = new Salerecord();
		// sql = "f";
		sql = "select * from Salerecord where createTime > ? order by createTime";
		Object[] params = new Object[] { createTime };
		manager.connectDB();
		rs = manager.executeQuery(sql, params);
		while (rs.next()) {
			sale = new Salerecord(
					rs.getString("createTime"),
					rs.getFloat("cashTotal"),rs.getFloat("cardTotal"),
					rs.getString("itemNo"),
					rs.getString("closeTime"), 
					rs.getString("deskName"),rs.getString("otherSpec"),
					rs.getString("OtherSpecNo1"), rs.getString("OtherSpecNo2"),
					rs.getString("status"),rs.getString("Waiter"),rs.getString("dept"),
					(float)(rs.getDouble("subTotal")),(float)(rs.getDouble("tipTotal")),
					(float)(rs.getDouble("total")),(float)(rs.getDouble("initTotal")),
					(float)(rs.getDouble("rebate")),(float)(rs.getDouble("taxTotal")),(int)(rs.getDouble("customerId"))
					);
			sales.add(sale);
		}
		manager.closeDB();
		return sales;
	}//curl -d "createTime=2015-01-01 00:00:00" "http://192.168.1.249:8080/Backoffice/GetSalerecord/"

	/**
	 * 插入销售菜单记录信息
	 * 
	 * @return
	 * @throws SQLException
	 */
	public boolean Exist(String itemNo) throws SQLException{
		String result = "";
		sql = "Select itemNo from Salerecord where itemNo=?";
		Object[] params = new Object[] { itemNo };
		manager.connectDB();
		rs = manager.executeQuery(sql, params);
		if (rs.next()) {
			result = rs.getString("itemNo");
		}
		manager.closeDB();
		return result.equals(itemNo);
	}
	public int saveSaleRecord(List<Salerecord> records) throws SQLException {
		int result = 0;
		final int size = records.size();
		sql = "insert into Salerecord(itemNo,closeTime,createTime,deskName,otherSpec,otherSpecNo1,otherSpecNo2,status,"
					+ "waiter,dept,subTotal,tipTotal,total,initTotal,rebate,taxTotal,customerId) "
					+ "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		Object[][] params = new Object[size][17];
		for (int i = 0; i < records.size(); i++) {
			params[i][0] = records.get(i).getItemNo();
			params[i][1] = records.get(i).getCloseTime();
			params[i][2] = records.get(i).getCreateTime();
				
			params[i][3] = records.get(i).getDeskName();
			params[i][4] = records.get(i).getOtherSpec();
			params[i][5] = records.get(i).getOtherSpecNo1();
			params[i][6] = records.get(i).getOtherSpecNo2();
			params[i][7] = records.get(i).getStatus();
			params[i][8] = records.get(i).getWaiter();
			params[i][9] = records.get(i).getDept();
			params[i][10] = records.get(i).getSubTotal();//float
			params[i][11] = records.get(i).getTipTotal();//float
			params[i][12] = records.get(i).getTotal();//float
			params[i][13] = records.get(i).getInitTotal();//float
			params[i][14] = records.get(i).getRebate();//float
			params[i][15] = records.get(i).getTaxTotal();//float
			params[i][16] = records.get(i).getCustomerId();//int
		}
		manager.connectDB();
		result = manager.executeBatchInsert(sql, params);
		manager.closeDB();
		return result;
	}
	
	//更新销售菜单记录信息
	public boolean updateSaleRecord(String bILLID, String pdtCODE,
			String closeTime, String status) {
		boolean result = false;
		String sql = "update  Salerecord set status=?,closeTime=? where billid=? and pdtCode=?";
		try {
			manager.connectDB();
			result = manager.executeUpdate(sql, new Object[] { status,closeTime,
					bILLID, pdtCODE });
			manager.closeDB();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;

	}
	
	//更新销售菜单记录信息
	public boolean updateSaleRecord(Salerecord record) {
		boolean result = false;
		String sql = "update Salerecord set closeTime=?,createTime=?,deskName=?,"+
		"otherSpec=?,otherSpecNo1=?,otherSpecNo2=?,status=?,"+
		"waiter=?,dept=?,subTotal=?,tipTotal=?,total=?,initTotal=?,rebate=?,taxTotal=?,customerId=? where itemNo=?";
		try {
			manager.connectDB();
			result = manager.executeUpdate(sql, new Object[] {
					
				record.getCloseTime(),
				record.getCreateTime(),							
				record.getDeskName(),
				record.getOtherSpec(),
				record.getOtherSpecNo1(),
				
				record.getOtherSpecNo2(),
				record.getStatus(),
				record.getWaiter(),
				record.getDept(),
				record.getSubTotal(),
				
				record.getTipTotal(),
				record.getTotal(),
				record.getInitTotal(),
				record.getRebate(),
				record.getTaxTotal(),
				
				record.getCustomerId(),
				record.getItemNo()
				});
				manager.closeDB();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return result;

		}	
	
	//得到状态为Done的销售菜单记录信息
	public List<Saleandpdt> getDoneSaleRecord(String id,String deskName)
			throws SQLException {
		String result="";
		List<Saleandpdt> sales = new ArrayList<Saleandpdt>();
		sql = "select * from Salerecord where deskName=? and itemNo=? ";
		Object[] params = new Object[] { deskName,id };
		manager.connectDB();
		rs = manager.executeQuery(sql, params);
		
		while (rs.next()) {
			result=rs.getString("ItemNO");
		}
		System.out.println(result);
		sql = "select * from Saleandpdt where salerecordId=? and status='Done'";
		params = new Object[] { result };
		manager.connectDB();
		rs = manager.executeQuery(sql, params);
		Saleandpdt sap=new Saleandpdt();
		while (rs.next()) {
			sap.setId(rs.getInt("id"));
			sap.setPdtCode(rs.getString("pdtCode"));
			sap.setStatus(rs.getString("status"));
			sap.setNumber(rs.getInt("number"));
			sap.setPdtName(rs.getString("pdtName"));
			sap.setPrice(rs.getFloat("price"));
			sap.setCreateTime(rs.getString("createTime"));
			sap.setCloseTime(rs.getString("closeTime"));
			sap.setPriority(rs.getInt("priority"));
			sales.add(sap);
		}
		manager.closeDB();
		return sales;

	}
	
	//判断是否为饮品
	public boolean isDrink(String ptdCode)
			throws SQLException {
		String result = "";
		sql = "Select TypeName from Product as p join Menutype as mt on mt.TypeId=p.typeId where p.pdtCode=?";
		Object[] params = new Object[] { ptdCode };
		manager.connectDB();
		rs = manager.executeQuery(sql, params);
		if (rs.next()) {
			result = rs.getString(1);
		}
		manager.closeDB();
		return result.equals("Drink");

	}
	
	//得到销售菜单的优先级
	
	public String getSaleAndPdtPriority(String id)
			throws SQLException {
		String result = "";
		sql = "Select priority from Saleandpdt where id=?";
		Object[] params = new Object[] { id };
		manager.connectDB();
		rs = manager.executeQuery(sql, params);
		if (rs.next()) {
			result = rs.getString("priority");
		}
		manager.closeDB();
		return result;

	}
	
	
	//得到销售菜单的优先级
	
	public int setSaleandpdtPriority(String salerecordId,String priority)
			throws SQLException {
		boolean rs;
		int result;
		sql = "update Saleandpdt set priority=?  where salerecordId=?";
		Object[] params = new Object[] { priority,salerecordId };
		manager.connectDB();
		rs = manager.executeUpdate(sql, params);
		manager.closeDB();
		if(rs){
			result=1;
		}
		else{
			result=0;
		}
		return result;
	}
	
	
	//根据waiter得到记录
  	public List<Salerecord> getSaleByWaiter(String waiter)
  			throws SQLException {
  		String result="";
  		List<Salerecord> sales = new ArrayList<Salerecord>();
  		sql = "select * from Salerecord where waiter=? ";
  		Object[] params = new Object[] { waiter };
  		manager.connectDB();
  	
  		rs = manager.executeQuery(sql, params);
  		Salerecord sap=new Salerecord();
  		while (rs.next()) {
  			sap.setItemNo(rs.getString("itemNo"));
  			sap.setCloseTime(rs.getString("closeTime"));
  			sap.setCreateTime(rs.getString("createTime"));
  			sap.setDeskName(rs.getString("deskName"));
  			sap.setStatus(rs.getString("deskName"));
  			sap.setSubTotal(rs.getFloat("subTotal"));
  			sap.setTipTotal(rs.getFloat("tipTotal"));
  			sap.setTotal(rs.getFloat("total"));
  			sap.setInitTotal(rs.getFloat("initTotal"));
  			sap.setRebate(rs.getFloat("rebate"));
  			sap.setTaxTotal(rs.getFloat("taxTotal"));
  			sap.setCashTotal(rs.getFloat("cashTotal"));
  			sap.setCardTotal(rs.getFloat("cardTotal"));
  			sap.setCustomerId(rs.getInt("customerId"));
  			sap.setWaiter(rs.getString("waiter"));
  			sap.setDept(rs.getString("dept"));
  			sap.setOtherSpec(rs.getString("otherSpec"));
  			sap.setOtherSpecNo1(rs.getString("otherSpecNo1"));
  			sap.setOtherSpecNo2(rs.getString("otherSpecNo2"));
  			sales.add(sap);
  		}
  		manager.closeDB();
  		return sales;
  	}
}